package han.study.preparedstatement.curd;

import han.study.bean.Customer;
import han.study.utils.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;

public class CustomerForQuery {
    @Test
    public void testQuery1(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try{
            conn = JDBCUtils.getConnection();
            String sql = "select * from customers where id=1";
            ps = conn.prepareStatement(sql);
            // 返回结果集
            resultSet = ps.executeQuery();
            // 处理方式跟迭代器的处理方式类似
            if(resultSet.next()){ // 判断结果集的下一条是否有数据，如果返回true，指针下移
                // 获取当前指针指向数据的各属性
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birthday = resultSet.getDate(4);
                // 方式一
                System.out.println("id="+id+",name="+name+",email="+email+",birthday="+birthday);
                // 方式二
                Object[] data = new Object[]{id,name,email,birthday};
                for (Object item: data) {
                    System.out.print(item + " ");
                }
                System.out.println();
                // 方式三 使用对象的范式 （java Bean）
                Customer customer = new Customer(id,name,email,birthday);
                System.out.println(customer);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCUtils.closeResource(conn,ps,resultSet);
        }
        System.out.println("查询结果结束");
    }

    /**
     * 通用查询方法
     */
    public Customer queryCustomer(String sql,Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            // 获取数据集元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 通过 ResultSetMetaData 获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                Customer customer = new Customer();
                for(int i=1; i<=columnCount;i++){
                    Object columnValue = rs.getObject(i);
                    // 获取了具体数据之后 如果构造一个对象,需要为对象中某个属性赋值
                    // 想办法获取结果集中的列名
                    // 获取结果集中的列名；通过反射来获取
                    String columnName = rsmd.getColumnName(i);
                    // 通过反射 找到属性来进行赋值
                    // 给customer对象指定columnName属性，赋值为columnValue,通过反射
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(customer,columnValue);
                }
                return customer;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }
        return null;
    }
    @Test
    public void testQuery(){
        String sql = "select id,name,email,birth from customers where id=?";
        Customer customer = queryCustomer(sql,1);
        System.out.println(customer);
    }
}
